package com.salesmonitoring.accounts.dao;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import com.salesmonitoring.connector.Connector;
import com.salesmonitoring.model.Motor;
import com.salesmonitoring.model.Transaction;

public class MarketingAssistantDaoImpl {

	private static MarketingAssistantDaoImpl marketingAssistantDaoImpl;
	private Connector connector;
	private Connection connection;
	private Statement statement;
	private String query;
	private ResultSet rs;
	
	public static synchronized MarketingAssistantDaoImpl getInstance() {
		if(marketingAssistantDaoImpl == null) 
			marketingAssistantDaoImpl = new MarketingAssistantDaoImpl();
		
		return marketingAssistantDaoImpl;
	}
	
	public void insertUser(String name, String email, Integer contact) {
		
		connector = Connector.getInstance();
		query = "INSERT INTO user (user_name,user_username, user_email, user_contact) VALUES('kikiam', '"+name+"', '"+email+"', '"+contact+"')";
		try {
			connection = connector.getConnection();
			statement = connection.createStatement();
			statement.executeUpdate(query);
			
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			
			if(statement != null) {
				try { statement.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(connection != null) {
				try { connection.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
		}
		
	}
	
	public int addReport(ArrayList<Object> details) {
		
		int result = 0;
		
		try {
			
			connector = Connector.getInstance();
			connection = connector.getConnection();
			statement = connection.createStatement();
			
			query = "INSERT INTO transaction(ao_id, old_customer, new_customer, rs_id, motor_model," +
					"motor_engine, motor_chassis, motor_plate, motor_class, motor_warehouse, " +
					"date_requested, date_repo, date_sold, account_number, asmf) " +
					"VALUES('" + details.get(0) + "', '" + details.get(1) + "', '" + details.get(2) + "', '" + details.get(3) + "', " +
							"'" + details.get(4) + "', '" + details.get(5) + "', '" + details.get(6) + "', '" + details.get(7) + "', " +
							"'" + details.get(8) + "', '" + details.get(9) + "', '" + details.get(10) + "', '" + details.get(11) + "', " +
							"'" + details.get(12) + "', '" + details.get(13) + "', '" + details.get(14) + "')";
			
			result = statement.executeUpdate(query);
			
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(statement != null) {
				try { statement.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(connection != null) {
				try { connection.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
		}
		
		return result;
		
	}
	
	public ArrayList<Transaction> getReports(String officer, String month, String year) {
		
		
		ArrayList<Transaction> reports = new ArrayList<Transaction>();

		if(!officer.equals("") && !month.equals("") && !year.equals("")) {
			query = "SELECT * FROM transaction WHERE ao_id='"+officer+"' AND date_requested LIKE '"+year+"-"+month+"-%'";
	
		} else if(!officer.equals("") && month.equals("") && !year.equals("")) {
			query = "SELECT * FROM transaction WHERE ao_id='"+officer+"' AND date_requested LIKE '"+year+"-%-%'";
		
		} else if(!officer.equals("") && !month.equals("") && year.equals("")) {
			query = "SELECT * FROM transaction WHERE ao_id='"+officer+"' AND date_requested LIKE '%-"+month+"-%'";
		
		}else if(officer.equals("") && !month.equals("") && !year.equals("")) {
			query = "SELECT * FROM transaction WHERE date_requested LIKE '"+year+"-"+month+"-%'";
		
		} else if(officer.equals("") && month.equals("") && !year.equals("")) {
			query = "SELECT * FROM transaction WHERE date_requested LIKE '"+year+"-%-%'";
		
		} else if(!officer.equals("") && month.equals("") && year.equals("")) {
			query = "SELECT * FROM transaction WHERE ao_id='"+officer+"'";
		
		} else if(officer.equals("") && !month.equals("") && year.equals("")) {
			query = "SELECT * FROM transaction WHERE date_requested LIKE '%-"+month+"-%'";
		
		} else {
			query = "SELECT * FROM transaction";
		}
		
		try {
			
			connector = Connector.getInstance();
			connection = connector.getConnection();
			statement = connection.createStatement();
			
			rs = statement.executeQuery(query);
			
			while(rs.next()) {
				
				Transaction t = new Transaction();
				
				t.setAo(rs.getString("ao_id"));
				t.setAccountNumber(Integer.parseInt(rs.getString("account_number")));
				t.setOldCustomer(rs.getString("old_customer"));
				t.setNewCustomer(rs.getString("new_customer"));
				t.setRs(rs.getString("rs_id"));
				t.setMotorModel(rs.getString("motor_model"));
				t.setMotorEngine(rs.getString("motor_engine"));
				t.setMotorChassis(Integer.parseInt(rs.getString("motor_chassis")));
				t.setMotorPlate(rs.getString("motor_plate"));
				t.setMotorClass(rs.getString("motor_class"));
				t.setAsmf(Integer.parseInt(rs.getString("asmf")));
				t.setMotorWarehouse(rs.getString("motor_warehouse"));
				t.setDateRequested(rs.getString("date_requested"));
				t.setDateRepo(rs.getString("date_repo"));
				t.setDateSold(rs.getString("date_sold"));
				
				reports.add(t);
				
			}
			
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(rs != null) {
				try { rs.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(statement != null) {
				try { statement.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(connection != null) {
				try { connection.close(); } catch(Exception e) { e.printStackTrace(); }
			}
		}
		
		return reports;
		
	}
	
	public String[] getAllStringEngineNumbers() {
		
		ArrayList<String> engineNumberList = new ArrayList<String>();
		String[] engineNumbers = null;
		
		engineNumberList.add("");
		
		try {
			connector = Connector.getInstance();
			connection = connector.getConnection();
			statement = connection.createStatement();
			
			query = "select motor_engine_number from motor";
			rs = statement.executeQuery(query);
			
			while(rs.next()) {
				engineNumberList.add(rs.getString("motor_engine_number"));
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(rs != null) {
				try { rs.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(statement != null) {
				try { statement.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(connection != null) {
				try { connection.close(); } catch(Exception e) { e.printStackTrace(); }
			}
		}
		
		engineNumbers = engineNumberList.toArray(new String[engineNumberList.size()]);
		
		return engineNumbers;
		
	}
	
	public Motor getMotorByEngineNumber(String number) {
		
		Motor motor = new Motor();
		motor.setEngineNumber(number);
		
		try {
			connector = Connector.getInstance();
			connection = connector.getConnection();
			statement = connection.createStatement();
			
			query = "select * from motor where motor_engine_number=" + number;
			rs = statement.executeQuery(query);
			
			while(rs.next()) {
				motor.setChassisNumber(rs.getString("motor_chasis_number"));
				motor.setPlateNumber(rs.getString("motor_plate_number"));
				motor.setModel(rs.getString("motor_model"));
				motor.setMotorClass(rs.getString("motor_class"));
				motor.setAsmf(rs.getString("motor_asmf"));
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(rs != null) {
				try { rs.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(statement != null) {
				try { statement.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(connection != null) {
				try { connection.close(); } catch(Exception e) { e.printStackTrace(); }
			}
		}
		
		return motor;
		
	}
	
}
